<?php
/**
 * OS CRM SYSTEM - An open source application development framework for PHP 4.3.2 or newer
 *
 * Model CLASS - DB F(Frontend) Model - OS Admin
 * 
 * @name      DB F(Frontend) Model
 * @package		OS CRM
 * @category  Class
 * @subcategory  Frontend Model
 * @author		CRM SYSTEM Dev Team
 * @copyright	Copyright (c) 2010 - 2012, CRM SYSTEM
 * @since     Version 1.0
 * @filesource
 * @access    public 
 */

// ------------------------------------------------------------------------

class DB_F_Model extends Model {

  //set db - listing data
  var $e_list_limit     = '50';
  var $e_list_offset    = '0';
  var $e_list_order     = 'asc';
  var $e_list_order_by  = 'os_journals_title';
  var $e_list_id        = 'os_journals_id';
  
  function __construct() {
    // Call the Model constructor
    parent::__construct();
  }

  function fr_get_stats_data(){
    $data = array();
    $usr_info = $this->session->userdata('usr_info');	
    $data['exp_date'] = __set_date($usr_info['usr_expiry'], 4);
    /*get fav list*/
    $qry = "SELECT j.os_journals_id, j.os_journals_title, j.os_journals_issn
            FROM ". TBL_MEMBERS_FAV ." f
            JOIN ". TBL_JOURNALS ."  j ON f.os_journals_id 	 = j.os_journals_id 	
            WHERE f.os_mem_id = '" . $usr_info['usr_id'] . "'
            AND j.os_journals_status = '1' 
            ORDER BY os_mem_fav_id desc";    
    $set = run_cust_qry($qry);
    $data['j_fav_list'] = $set['list'];
    
    /*get alert list*/
    $qry = "SELECT j.os_journals_id, j.os_journals_title, j.os_journals_issn
            FROM ". TBL_MEMBERS_ALERTS ." f
            JOIN ". TBL_JOURNALS ."  j ON f.os_journals_id 	 = j.os_journals_id 	
            WHERE f.os_mem_id = '" . $usr_info['usr_id'] . "'
            AND j.os_journals_status = '1'  
            ORDER BY os_mem_alerts_id desc";    
    $set = run_cust_qry($qry);
    $data['j_alert_list'] = $set['list'];
    
    /*get new publication list*/
    $qry = "SELECT os_pub_id as `id`,	os_pub_name as `name`
            FROM ". TBL_PUB ."
            WHERE os_pub_status = '1'  
            ORDER BY os_pub_id desc, os_pub_name
            LIMIT 5
            ";    
    //__debug($qry);exit;
    $set = run_cust_qry($qry);
    $data['new_pub'] = $set['list'];
    
    $months_back = 1;    
    $dt_from = mktime( date("H", now()), date("i", now()), date("s", now()), (date("m", now()) - $months_back), date("d", now()), date("Y", now()));
    $dt_to = mktime( date("H", now()), date("i", now()), date("s", now()), date("m", now()), date("d", now()), date("Y", now()));
    $data['dt_from'] = standard_date('DATE_OS_CRM_1',$dt_from);
    $data['dt_to'] = standard_date('DATE_OS_CRM_1',$dt_to);
    
    /*__debug(unix_to_human($dt_from,TRUE,'n'));
    __debug(unix_to_human($dt_to,TRUE,'n'));
    exit();*/
    
    /*get most read ARTICLES*/
    $qry = "SELECT count(rd.os_stats_most_read_id) as cnt, rd.os_articles_id as `id`, 
            art.os_articles_title as `name`, art.os_journals_id as `jid`
            FROM ".TBL_STATS_MOST_RD." rd
            JOIN ".TBL_ARTICLES." art
            ON rd.os_articles_id = art.os_articles_id
            WHERE
            os_stats_most_read_created BETWEEN '".unix_to_human($dt_from,TRUE,'n')."' AND '".unix_to_human($dt_to,TRUE,'n')."'
            GROUP BY rd.os_articles_id
            ORDER by cnt desc
            LIMIT 5
            ";    
    //__debug($qry);exit;
    $set = run_cust_qry($qry);
    $data['stats_most_read'] = $set['list'];
    
    /*get most read PUBLICATIONS*/
    $qry = "SELECT COUNT(st.os_stats_most_read_pub_id) as cnt, pub.os_pub_id as `pid`, pub.os_pub_name as `name`
            FROM ".TBL_STATS_MOST_RD_PUB." st
            JOIN ".TBL_PUB." pub
            ON st.os_pub_id = pub.os_pub_id  
            WHERE
            os_stats_most_read_pub_created BETWEEN '".unix_to_human($dt_from,TRUE,'n')."' AND '".unix_to_human($dt_to,TRUE,'n')."'
            GROUP BY pid
            ORDER BY cnt desc
            LIMIT 5";    
    //__debug($qry);exit;
    $set = run_cust_qry($qry);
    $data['stats_most_read_pub'] = $set['list'];
    
    /*get most visited PUBLICATIONS*/
    $qry = "SELECT COUNT(st.os_stats_most_vis_pub_id) as cnt, pub.os_pub_id as `pid`, pub.os_pub_name as `name`
            FROM ".TBL_STATS_MOST_VIS_PUB." st
            JOIN ".TBL_PUB." pub
            ON st.os_pub_id = pub.os_pub_id   
            WHERE
            os_stats_most_vis_pub_created BETWEEN '".unix_to_human($dt_from,TRUE,'n')."' AND '".unix_to_human($dt_to,TRUE,'n')."'
            GROUP BY pid
            ORDER BY cnt desc
            LIMIT 5";    
    //__debug($qry);exit;
    $set = run_cust_qry($qry);
    $data['stats_most_vis_pub'] = $set['list'];
    return $data;
  }
}
?>
